<?php
/**
 * TomatoCMS
 *
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@tomatocms.com so we can send you a copy immediately.
 *
 * @copyright    Copyright (c) 2009-2010 TIG Corporation (http://www.tig.vn)
 * @license        http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @version     $Id: Category.php 2870 2010-05-17 03:04:13Z huuphuoc $
 * @since        2.0.5
 */

class Shop_Models_Dao_Pdo_Mysql_Category
    extends Tomato_Model_Dao
    implements Shop_Models_Interface_Category
{
    public function convert($entity)
    {
        return new Shop_Models_Category($entity);
    }

    public function getCategoryTree()
    {
        $sql = 'SELECT node.category_id, node.category_parent_id, node.category_admin_name, node.category_status, node.category_enable_product, (COUNT(parent.category_admin_name) - 1) AS depth,
                    node.category_left, node.category_right
                FROM '.$this->_prefix.'shop_category AS node,
                    '.$this->_prefix.'shop_category AS parent
                WHERE node.category_left BETWEEN parent.category_left AND parent.category_right
                GROUP BY node.category_id
                ORDER BY node.category_left';
        $rs = $this->_conn->query($sql)->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function add($category, $parentId = null)
    {
        if ($parentId) {
            $sql = 'SELECT category_right FROM '.$this->_prefix.'shop_category WHERE category_id = '.$this->_conn->quote($parentId);
        } else {
            $sql = 'SELECT MAX(category_right) AS category_right FROM '.$this->_prefix.'shop_category';
        }
        $right = $this->_conn->query($sql)->fetch();
        $rightId = ($parentId) ? $right->category_right : $right->category_right + 1;
        if ($rightId != null) {
            $sql = 'UPDATE '.$this->_prefix.'shop_category
                    SET category_left = IF(category_left > '.$this->_conn->quote($rightId).', category_left + 2, category_left),
                        category_right = IF(category_right >= '.$this->_conn->quote($rightId).', category_right + 2, category_right)';
            $this->_conn->query($sql);

            $data = array(
                'category_parent_id'        => $category->category_parent_id,
                'category_admin_name'        => $category->category_admin_name,
                'category_status'               => $category->category_status,
                'category_image'               => $category->category_image,
                'category_enable_product'    => $category->category_enable_product,
                'category_created_date'           => $category->category_created_date,
                'category_modified_date'    => $category->category_modified_date,
                'category_left'                   => $rightId,
                'category_right'               => $rightId + 1,
            );
            // TODO: Why do we need this?
            if (isset($category->category_id) && $category->category_id != null) {
                $data['category_id'] = $category->category_id;
            }
            $this->_conn->insert($this->_prefix.'shop_category', $data);
            return $this->_conn->lastInsertId($this->_prefix.'shop_category');
        }
    }

    public function getLanguage(){
        $select = $this->_conn
                        ->select()
                        ->from(array('l' => $this->_prefix.'language_key'));
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function getCategoryById($id, $status = null)
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('c' => $this->_prefix.'shop_category'))
                        ->joinLeft(array('cc' => $this->_prefix.'shop_category_content'), "c.category_id = cc.category_id AND cc.language_code = '{$this->_lang}'", array('category_content_id', 'language_code', 'category_content_name', 'category_content_friendly_url', 'category_content_description'))
                        ->where('c.category_id = ?', $id);
                        if($status){
                        $select->where('c.category_status = ?', $status);
                        }
                        $select->limit(1);
        $row = $select->query()->fetchAll();
        $categories = new Tomato_Model_RecordSet($row, $this);
        return (count($categories) == 0) ? null : $categories[0];
    }

    public function getParentCategory($category)
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('c' => $this->_prefix.'shop_category'))
                        ->where('c.category_left < '.$this->_conn->quote($category->category_left))
                        ->where('c.category_right > '.$this->_conn->quote($category->category_right))
                        ->order('c.category_left DESC')
                        ->limit(1);
        $row = $select->query()->fetchAll();
        $categories = new Tomato_Model_RecordSet($row, $this);
        return (count($categories) == 0) ? null : $categories[0];
    }

    public function update($category, $parentId = null, $deleteCategory = false, $includeChild = true)
    {
        if ($deleteCategory) {
            if ($includeChild) {
                $oldCategories = $this->getSubCategories($category->category_id, null);
                if (count($oldCategories) > 0) {
                    /**
                    * Delete category
                    */
                    $width = $category->category_right - $category->category_left + 1;
                    $sql = 'DELETE FROM '.$this->_prefix.'shop_category
                            WHERE category_left BETWEEN '.$category->category_left.' AND '.$category->category_right;
                    $this->_conn->query($sql);
                    //delete category content belong to this category
                    $sql = 'DELETE FROM '.$this->_prefix.'shop_category_content
                            WHERE category_id = '.$category->category_id;
                    $this->_conn->query($sql);

                    $sql = 'UPDATE '.$this->_prefix.'shop_category
                            SET category_right = category_right - '.$width.'
                            WHERE category_right > '.$category->category_right;
                    $this->_conn->query($sql);
                    $sql = 'UPDATE '.$this->_prefix.'shop_category
                            SET category_left = category_left - '.$width.'
                            WHERE category_left > '.$category->category_right;
                    $this->_conn->query($sql);

                    /**
                    * Add category
                    */
                    $preDepth = null;
                    $preCategoryId = null;
                    foreach ($oldCategories as $oldCategory) {
                        $parentId = (null != $preDepth && $oldCategory->depth > $preDepth)
                                                    ? $preCategoryId : $parentId;
                        $this->add($oldCategory, $parentId);
                        $preDepth = $oldCategory->depth;
                        $preCategoryId = $oldCategory->category_id;
                    }
                }
            } else {
                $this->delete($category);
                $this->add($category, $parentId);
            }
        } else {
            $where[] = 'category_id = '.$this->_conn->quote($category->category_id);
            $this->_conn->update($this->_prefix.'shop_category', array(
                                    'category_admin_name'         => $category->category_admin_name,
                                    'category_status'             => $category->category_status,
                                    'category_image'             => $category->category_image,
                                    'category_enable_product'    => $category->category_enable_product,
                                    'category_modified_date'     => $category->category_modified_date,
                                ), $where);
        }
    }

    public function delete($category)
    {
        $where[] = 'category_id = '.$this->_conn->quote($category->category_id);
        $this->_conn->delete($this->_prefix.'shop_category', $where);

        /**
         * delete category content belong to this category
         **/
        $this->_conn->delete($this->_prefix.'shop_category_content', $where);

        $sql = 'UPDATE '.$this->_prefix.'shop_category
                SET category_left = category_left - 1, category_right = category_right - 1
                WHERE category_left BETWEEN '.$category->category_left.' AND '.$category->category_right;
        $this->_conn->query($sql);

        $sql = 'UPDATE '.$this->_prefix.'shop_category
                SET category_right = category_right - 2
                WHERE category_right > '.$this->_conn->quote($category->category_right);
        $this->_conn->query($sql);

        $sql = 'UPDATE '.$this->_prefix.'shop_category
                SET category_left = category_left - 2
                WHERE category_left > '.$this->_conn->quote($category->category_right);
        $this->_conn->query($sql);
    }
    public function inactive($category_id){
        $where[] = 'category_id = '.$this->_conn->quote($category_id);
        $this->_conn->update($this->_prefix.'shop_category', array(
                                    'category_status'             => 0
                                ), $where);
    }
    public function getAllAttributes(){
        $select = $this->_conn
                        ->select()
                        ->from(array('at' => $this->_prefix.'shop_attribute_type'), array('category_id'));
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }
    public function getAllProducts(){
        $select = $this->_conn
                        ->select()
                        ->from(array('at' => $this->_prefix.'shop_product'), array('category_id'));
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function updateOrder($category)
    {
        $this->_conn->update($this->_prefix . 'shop_category',
                            array(
                                'category_parent_id' => $category->category_parent_id,
                                'category_left'   => $category->category_left,
                                'category_right'  => $category->category_right,
                            ),
                            array(
                                'category_id = ?' => $category->category_id,
                            ));
    }

    /*----------------------------------Front-end Actions--------------------------------------------*/
    public function getDirSubCategory($id){
        $select = $this->_conn
                        ->select()
                        ->from(array('c' => $this->_prefix.'shop_category'))
                        ->joinLeft(array('cc' => $this->_prefix.'shop_category_content'), "c.category_id = cc.category_id AND cc.language_code = '{$this->_lang}'", array('category_content_id', 'language_code', 'category_content_name', 'slug' => 'category_content_friendly_url', 'category_content_description'))
                        ->where('c.category_parent_id = ?', $id)
                        ->where('c.category_status = ?', 1)
                        ;
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function getSubCategory($category){
        $select = $this->_conn
                        ->select()
                        ->from(array('c' => $this->_prefix.'shop_category'))
                        ->joinLeft(array('cc' => $this->_prefix.'shop_category_content'), "c.category_id = cc.category_id AND cc.language_code = '{$this->_lang}'", array('category_content_id', 'language_code', 'category_content_name', 'category_content_friendly_url', 'category_content_description'))
                        ->where('c.category_left > ?', $category->category_left)
                        ->where('c.category_right < ?', $category->category_right)
                        ->where('c.category_status = ?', 1)
                        ;
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function getFTree()
    {
        $sql = "SELECT c.* , cc.category_content_name, cc.language_code, cc.category_content_id, cc.category_content_friendly_url as slug, cc.category_content_description
                        FROM (SELECT node.*, (COUNT(parent.category_admin_name) - 1) AS depth
                        FROM {$this->_prefix}shop_category AS node, {$this->_prefix}shop_category AS parent WHERE node.category_left
                        BETWEEN parent.category_left AND parent.category_right GROUP BY node.category_id ORDER BY node.category_left) as c
                        LEFT JOIN {$this->_prefix}shop_category_content as cc ON c.`category_id` = cc.`category_id` AND cc.language_code = '{$this->_lang}'";

        $rs = $this->_conn->query($sql)->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function getById($id){
        $select = $this->_conn
                        ->select()
                        ->from(array('c' => $this->_prefix.'shop_category'))
                        ->joinLeft(array('cc' => $this->_prefix.'shop_category_content'), "c.category_id = cc.category_id AND cc.language_code = '{$this->_lang}'", array('category_content_id', 'language_code', 'category_content_name', 'slug' => 'category_content_friendly_url', 'category_content_description'))
                        ->where('c.category_id = ?', $id)
                        ->where('c.category_status = ?', 1)
                        ->limit(1);
                        ;
        $row = $select->query()->fetchAll();
        $categorys = new Tomato_Model_RecordSet($row, $this);
        return (count($categorys) == 0) ? null : $categorys[0];
    }

    public function getAllSub($id)
    {
        $rs = $this->_conn
                    ->select()
                    ->from(array('node' => $this->_prefix . 'shop_category'), array())
                    ->from(array('parent' => $this->_prefix . 'shop_category'))
                    ->joinLeft(array('cc' => 'shop_category_content'), "parent.category_id = cc.category_id AND cc.language_code = '{$this->_lang}'", array('category_content_id', 'language_code', 'category_content_name', 'slug' => 'category_content_friendly_url', 'category_content_description'))
                    ->where('parent.category_left BETWEEN node.category_left AND node.category_right')
                    ->where('node.category_id = ?', $id)
                    ->order('parent.category_left')
                    ->query()
                    ->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function getParents($categoryId)
    {
        $rs = $this->_conn
                    ->select()
                    ->from(array('node' => $this->_prefix . 'shop_category'), array())
                    ->from(array('parent' => $this->_prefix . 'shop_category'))
                    ->joinLeft(array('cc' => 'shop_category_content'), "parent.category_id = cc.category_id AND cc.language_code = '{$this->_lang}'", array('category_content_id', 'language_code', 'category_content_name', 'slug' => 'category_content_friendly_url', 'category_content_description'))
                    ->where('node.category_left BETWEEN parent.category_left AND parent.category_right')
                    ->where('node.category_id = ?', $categoryId)
                    ->order('parent.category_left')
                    ->query()
                    ->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }
}

